﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DBFunc
{
    public class FilialDBFunc
    {
        private DataSet FilialDS = new DataSet("FilialDS");

        private SqlConnection FilialConnection = null;
        private SqlDataAdapter dAdaptProduct = null;

        public FilialDBFunc(string cnString)
        {
            FilialConnection =new SqlConnection(cnString);
            ConfigureProductAdapter(out dAdaptProduct);
        }

        private void ConfigureProductAdapter(out SqlDataAdapter dAdapt)
        {
            //Select command
            SqlCommand SelectCmd = new SqlCommand("GetAllProducts", FilialConnection);
            SelectCmd.CommandType = CommandType.StoredProcedure;
            dAdapt = new SqlDataAdapter(SelectCmd);


            // Create the InsertCommand.
            SqlCommand InsProduct = new SqlCommand("InsertProduct",FilialConnection);
            InsProduct.CommandType = CommandType.StoredProcedure;
            InsProduct.Parameters.Add("@ProductID", SqlDbType.NVarChar, 20, "ProductID");
            InsProduct.Parameters.Add("@Name", SqlDbType.NVarChar, 30, "Name");
            InsProduct.Parameters.Add("@Price", SqlDbType.Money,50, "Price");
            InsProduct.Parameters.Add("@GrntPeriod", SqlDbType.Int, 50, "GrntPeriod");
            InsProduct.Parameters.Add("@Count", SqlDbType.Int, 50, "Count");

            //SqlParameter parameter = InsProduct.Parameters.Add("@iTariffPlan", SqlDbType.NVarChar, 50, "TariffPlan");
            //parameter.SourceVersion = DataRowVersion.Original;

            dAdapt.InsertCommand = InsProduct;

            //UpdateCommand
            SqlCommand UpdateProduct = new SqlCommand("UpdateProduct",FilialConnection);
            UpdateProduct.CommandType = CommandType.StoredProcedure;
            // Add the parameters for the UpdateCommand.
            UpdateProduct.Parameters.Add("@ProductID", SqlDbType.NVarChar, 20, "ProductID");
            UpdateProduct.Parameters.Add("@Name", SqlDbType.NVarChar, 30, "Name");
            UpdateProduct.Parameters.Add("@Price", SqlDbType.Money, 50, "Price");
            UpdateProduct.Parameters.Add("@GrntPeriod", SqlDbType.Int, 50, "GrntPeriod");
            UpdateProduct.Parameters.Add("@Count", SqlDbType.Int, 50, "Count");
            SqlParameter parameter = UpdateProduct.Parameters.Add(
            "@oldProductID", SqlDbType.NVarChar, 20, "ProductID");
            parameter.SourceVersion = DataRowVersion.Original;
            dAdapt.UpdateCommand = UpdateProduct;
            
            //Delete command
            SqlCommand DelProduct = new SqlCommand("DeleteProduct", FilialConnection);
            DelProduct.CommandType = CommandType.StoredProcedure;
            SqlParameter parameterID = DelProduct.Parameters.Add("@ProductID", SqlDbType.NVarChar, 20, "ProductID");
            parameterID.SourceVersion = DataRowVersion.Original; 
            dAdapt.DeleteCommand = DelProduct;
        }

        public DataTable GetAllProducts()
        {
            try
            {
                if (FilialDS.Tables["Product"] != null) FilialDS.Tables["Product"].Clear();
                dAdaptProduct.Fill(FilialDS, "Product");
                return FilialDS.Tables["Product"];
            }
            catch (Exception ex)
            {
                throw ex;
            };
        }

        public void UpdateProducts()
        {
            dAdaptProduct.Update(FilialDS, "Product");
        }

        public void DelProduct(string ProdID)
        {
            FilialDS.Tables["Product"].Select(string.Format("ProductID={0}", int.Parse(ProdID)))[0].Delete();
            dAdaptProduct.Update(FilialDS, "Product");
            FilialDS.AcceptChanges();
        }
    }
}
